package com.beverage.lemonade.databse;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.json.JSONException;

import com.beverage.lemonade.process.Constants;
import com.beverage.lemonade.process.ContentJson;
import com.beverage.lemonade.process.DeleteJson;
import com.beverage.lemonade.process.MyCalendar;
import com.beverage.lemonade.process.MyJson;
import com.beverage.lemonade.process.UserInfoJson;

import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.preference.PreferenceManager;

public class DataConnector {

	// 新しくMARKERを追加するとき,DATABASEは基本的に数字を保存する。この他に、CONSTANTSのCONTENTに追加する必要がある。

	protected static final String UserTable = "USER_TABLE";
	protected static final String RemainderTable = "REMAINDER_TABLE";

	protected SQLiteDatabase mydb;
	protected DataBaseHelper hlpr;
	protected Context context;

	public DataConnector(Context context) {
		this.context = context;
		this.hlpr = new DataBaseHelper(context);
	}

	public List<Map<String, Object>> getMonthLog(int year, int month) {
		mydb = hlpr.getWritableDatabase();

		int starttime = year * 10000 + month * 100;
		int endtime = year * 10000 + (month + 1) * 100;

		String starttimeStr = Integer.toString(starttime);
		String endtimeStr = Integer.toString(endtime);

		List<Map<String, Object>> InfoList = new ArrayList<Map<String, Object>>();
		Cursor mCursor = null;
		String query = "SELECT * FROM " + UserTable + " WHERE Date BETWEEN '"
				+ starttimeStr + "' AND '" + endtimeStr + "'";

		mCursor = mydb.rawQuery(query, null);

		while (mCursor.moveToNext()) {
			HashMap<String, Object> instantHash = new HashMap<String, Object>();
			int Date = mCursor.getInt(mCursor.getColumnIndex("DATE"));
			int Content = mCursor.getInt(mCursor.getColumnIndex("CONTENT"));
			instantHash.put(Constants.CONTENT[Content], Date);
			InfoList.add(instantHash);
		}

		mCursor.close();
		mydb.close();
		return InfoList;

	}

	public void deleteContent(int CONTENT_ID) {
		hlpr.deleteContent(CONTENT_ID);
	}

	public void deleteContent(int DATE, int CONTENT) {
		hlpr.deleteContent(DATE, CONTENT);
	}

	public void saveContent(int Year, int Month, int Date, int Content,
			String Memo) {
		hlpr.insertContent(MyCalendar.caculateDay(Year, Month, Date), Content,
				Memo, 0);
		if (Content == 0) {
			updatePeriodSpan();
		}
	}

	public void saveContent(int Year, int Month, int Date, int Content) {
		hlpr.insertContent(MyCalendar.caculateDay(Year, Month, Date), Content,
				null, 0);
		if (Content == 0) {
			updatePeriodSpan();
		}
	}

	public void saveContent(int Year, int Month, int Date, int Content,
			double Number) {
		hlpr.insertContent(MyCalendar.caculateDay(Year, Month, Date), Content,
				null, Number);
		if (Content == 0) {
			updatePeriodSpan();
		}
	}

	protected void saveContent(int Content_ID, int Update_Date, int Date,
			int Content, String Memo, double Number) {
		hlpr.insertContent(Content_ID, Date, Update_Date, Content, Memo, Number);
	}

	public void updatePeriodSpan() {
		mydb = hlpr.getWritableDatabase();

		Cursor mCursor = null;
		String query = "SELECT DATE FROM " + UserTable
				+ " WHERE CONTENT = '0' ORDER BY DATE ASC";

		mCursor = mydb.rawQuery(query, null);
		int times = 0;
		int firstDate = 0;
		int lastDate = 0;
		while (mCursor.moveToNext()) {
			if (times == 0) {
				firstDate = mCursor.getInt(mCursor.getColumnIndex("DATE"));
			}
			times++;
			lastDate = mCursor.getInt(mCursor.getColumnIndex("DATE"));
		}

		mCursor.close();
		mydb.close();

		if (times > 1) {
			int average = MyCalendar.differenceDays(firstDate, lastDate)
					/ (times - 1);
			SharedPreferences sp = PreferenceManager
					.getDefaultSharedPreferences(context);
			sp.edit().putInt(Constants.PERIOD_SPAN, average).commit();
		}
	}

	public int getFormer() {
		mydb = hlpr.getWritableDatabase();

		int date = 0;

		Cursor mCursor = null;

		String query = "SELECT MAX(DATE) FROM " + UserTable
				+ " WHERE CONTENT = '0'";

		mCursor = mydb.rawQuery(query, null);

		while (mCursor.moveToNext()) {
			// date = mCursor.getInt(mCursor.getColumnIndex("[MAX(DATE)]"));
			date = mCursor.getInt(0);

		}
		mCursor.close();
		mydb.close();
		return date;
	}

	public List<Map<String, Object>> getDateLog(int year, int month, int day) {
		mydb = hlpr.getWritableDatabase();

		String dateStr = Integer.toString(MyCalendar.caculateDay(year, month,
				day));
		Cursor mCursor = null;
		String query = "SELECT * FROM " + UserTable + " WHERE Date = '"
				+ dateStr + "'";

		mCursor = mydb.rawQuery(query, null);
		List<Map<String, Object>> InfoList = new ArrayList<Map<String, Object>>();

		while (mCursor.moveToNext()) {
			HashMap<String, Object> instantHash = new HashMap<String, Object>();

			int Content = mCursor.getInt(mCursor.getColumnIndex("CONTENT"));
			int Content_ID = mCursor.getInt(mCursor
					.getColumnIndex("CONTENT_ID"));
			double Number = mCursor.getDouble(mCursor.getColumnIndex("NUMBER"));
			String Memo = mCursor.getString(mCursor.getColumnIndex("MEMO"));
			int Update_date = mCursor.getInt(mCursor
					.getColumnIndex("UPDATE_DATE"));

			instantHash.put("CONTENT", Content);
			instantHash.put("CONTENT_ID", Content_ID);
			instantHash.put("NUMBER", Number);
			instantHash.put("MEMO", Memo);
			instantHash.put("UPDATE_DATE", Update_date);

			InfoList.add(instantHash);

		}
		mCursor.close();
		mydb.close();
		return InfoList;
	}

	public MyJson getAllContentByMyJson() throws JSONException {

		mydb = hlpr.getWritableDatabase();

		Cursor mCursor = null;
		String query = "SELECT * FROM " + UserTable + " ORDER BY DATE DESC";

		mCursor = mydb.rawQuery(query, null);

		List<ContentJson> InfoList = new ArrayList<ContentJson>();

		while (mCursor.moveToNext()) {

			int Content = mCursor.getInt(mCursor.getColumnIndex("CONTENT"));
			int Content_ID = mCursor.getInt(mCursor
					.getColumnIndex("CONTENT_ID"));
			double Number = mCursor.getDouble(mCursor.getColumnIndex("NUMBER"));
			String Memo = mCursor.getString(mCursor.getColumnIndex("MEMO"));
			int Update_date = mCursor.getInt(mCursor
					.getColumnIndex("UPDATE_DATE"));
			int Date = mCursor.getInt(mCursor.getColumnIndex("DATE"));

			ContentJson contentJson = new ContentJson(Content_ID, Update_date,
					Date, Content, Memo, Number);
			InfoList.add(contentJson);

		}
		mCursor.close();
		mydb.close();

		MyJson myjson = new MyJson(new UserInfoJson(context), InfoList);
		return myjson;
	}

	public MyJson getAddRemainder() throws JSONException {
		mydb = hlpr.getWritableDatabase();

		Cursor mCursor = null;
		String query = "SELECT　* FROM　" + UserTable + "," + RemainderTable
				+ "WHERE　" + RemainderTable + ".CONTENT_ID=" + UserTable
				+ ".CONTENT_ID AND " + RemainderTable + ".OPERATION='0'";

		mCursor = mydb.rawQuery(query, null);

		List<ContentJson> InfoList = new ArrayList<ContentJson>();

		while (mCursor.moveToNext()) {

			int Content = mCursor.getInt(mCursor.getColumnIndex("CONTENT"));
			int Content_ID = mCursor.getInt(mCursor
					.getColumnIndex("CONTENT_ID"));
			double Number = mCursor.getDouble(mCursor.getColumnIndex("NUMBER"));
			String Memo = mCursor.getString(mCursor.getColumnIndex("MEMO"));
			int Update_date = mCursor.getInt(mCursor
					.getColumnIndex("UPDATE_DATE"));
			int Date = mCursor.getInt(mCursor.getColumnIndex("DATE"));

			ContentJson contentJson = new ContentJson(Content_ID, Update_date,
					Date, Content, Memo, Number);

			InfoList.add(contentJson);

		}
		mCursor.close();
		mydb.close();

		MyJson myjson = new MyJson(new UserInfoJson(context), InfoList);

		return myjson;
	}

	public DeleteJson getDeleteRemainder() throws JSONException {

		mydb = hlpr.getWritableDatabase();

		Cursor mCursor = null;
		String query = "SELECT * FROM " + RemainderTable
				+ " WHERE OPERATION = '1'";

		mCursor = mydb.rawQuery(query, null);

		DeleteJson deleteJson = new DeleteJson();

		while (mCursor.moveToNext()) {

			int Content_ID = mCursor.getInt(mCursor
					.getColumnIndex("CONTENT_ID"));
			deleteJson.setDeleteID(Content_ID);

		}
		mCursor.close();
		mydb.close();

		return deleteJson;
	}

	public List<Map<String, Object>> getPeriodList() {
		mydb = hlpr.getWritableDatabase();

		Cursor mCursor = null;
		String query = "SELECT * FROM " + UserTable
				+ " WHERE CONTENT = '0' ORDER BY DATE DESC";

		mCursor = mydb.rawQuery(query, null);
		List<Map<String, Object>> InfoList = new ArrayList<Map<String, Object>>();

		int time = 0;
		int check = 0;
		while (mCursor.moveToNext()) {

			time++;

			HashMap<String, Object> instantHash = new HashMap<String, Object>();
			int Span = 0;
			int Date = mCursor.getInt(mCursor.getColumnIndex("DATE"));
			if (check != 0) {
				Span = MyCalendar.differenceDays(Date, check);
			}

			check = Date;

			instantHash.put("Date", Date);
			instantHash.put("Span", Span);
			instantHash.put("Time", time);

			InfoList.add(instantHash);

		}
		mCursor.close();
		mydb.close();
		return InfoList;
	}

}
